package lab;

/**
 *
 * @author yakimovich
 */

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author Yana
 */
public class JdbcUserDao extends AbstractJdbcDao
        implements UserDao {
    
        protected static org.apache.commons.logging.Log logger =
            LogFactory.getLog(JdbcUserDao.class);

    /**
     * Create JdbcUserDao by default.
     */
    public JdbcUserDao() {
        super();
    }

    /**
     * Create JdbcUserDao with the specified parameters.
     *
     * @param settings
     */
    public JdbcUserDao(String settings) {
        super(settings);
    }

    /**
     * Create in the table users a row with the specified user.
     *
     * @param user
     */
    @Override
    public void create(User user) {
        if (user == null) {
            throw new NullPointerException();
        }

        Connection conn = createConnection();
        PreparedStatement ps = null;

        long id = user.getId();
        String login = user.getLogin();
        String password = user.getPassword();
        String email = user.getEmail();
        String firstName = user.getFirstName();
        String lastName = user.getLastName();
        Date birthday = user.getBirthday();
        long role_id = user.getRole().getId();

        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("INSERT INTO USERS ("
                    + "LOGIN, PASSWORD, EMAIL,"
                    + "FIRST_NAME, LAST_NAME, BIRTHDAY, ROLE_ID) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?);");
            ps.setString(1, login);
            ps.setString(2, password);
            ps.setString(3, email);
            ps.setString(4, firstName);
            ps.setString(5, lastName);
            ps.setDate(6, birthday);
            ps.setLong(7, role_id);

            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Update in the table users a row with the specified user.
     *
     * @param user
     */
    @Override
    public void update(User user) {
        if (user == null) {
            throw new NullPointerException();
        }
        if (!isUserById(user.getId())) {
            throw new IllegalArgumentException();
        }

        Connection conn = createConnection();
        PreparedStatement ps = null;

        long id = user.getId();
        String login = user.getLogin();
        String password = user.getPassword();
        String email = user.getEmail();
        String firstName = user.getFirstName();
        String lastName = user.getLastName();
        Date birthday = user.getBirthday();
        long role_id = user.getRole().getId();

        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "UPDATE Users SET "
                    + "LOGIN = ?, PASSWORD = ?, EMAIL = ?, "
                    + "FIRST_NAME = ?, LAST_NAME = ?, BIRTHDAY = ?, "
                    + "ROLE_ID = ? "
                    + "WHERE User_id = ?");

            ps.setString(1, login);
            ps.setString(2, password);
            ps.setString(3, email);
            ps.setString(4, firstName);
            ps.setString(5, lastName);
            ps.setDate(6, birthday);
            ps.setLong(7, role_id);
            ps.setLong(8, id);

            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Remove in the table users a row with the specified user.
     *
     * @param user
     */
    @Override
    public void remove(User user) {
        if (user == null) {
            throw new NullPointerException();
        }
        if (!isUserById(user.getId())) {
            throw new IllegalArgumentException();
        }
        Connection conn = createConnection();
        PreparedStatement ps = null;

        long id = user.getId();

        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "DELETE FROM Users WHERE User_id = ?");
            ps.setLong(1, id);

            int result = ps.executeUpdate();
            conn.commit();
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Find in the table users all user.
     *
     * @return List of all users
     */
    @Override
    public List<User> findAll() {
        List<User> result = new ArrayList<User>();
        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "LEFT JOIN Roles "
                    + "ON Users.Role_id = Roles.Role_id");
            rs = ps.executeQuery();
            while (rs.next()) {
                long id = rs.getLong("USER_ID");
                String login = rs.getString("LOGIN");
                String password = rs.getString("PASSWORD");
                String email = rs.getString("EMAIL");
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                Date birthday = rs.getDate("BIRTHDAY");
                String role_name = rs.getString("NAME");

                User user = new User();
                user.setId(id);
                user.setLogin(login);
                user.setPassword(password);
                user.setEmail(email);
                user.setFirstName(firstName);
                user.setLastName(lastName);
                user.setBirthday(birthday);
                user.setRole(new JdbcRoleDao().findByName(role_name));

                result.add(user);
            }
            conn.commit();
            return result;
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Find in the table users a user with the specified login.
     *
     * @param login the specified login of a user
     * @return user with the specified login
     */
    @Override
    public User findByLogin(String login) {
        if (login == null) {
            throw new NullPointerException();
        }
        if (!isUserByLogin(login)) {
            throw new IllegalArgumentException();
        }
        User user = null;
        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "LEFT JOIN Roles "
                    + "ON Users.Role_id = Roles.Role_id "
                    + "WHERE login = ?");
            ps.setString(1, login);
            rs = ps.executeQuery();

            while (rs.next()) {
                long id = rs.getLong("USER_ID");
                String password = rs.getString("PASSWORD");
                String email = rs.getString("EMAIL");
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                Date birthday = rs.getDate("BIRTHDAY");
                String role_name = rs.getString("NAME");

                user = new User();
                user.setId(id);
                user.setLogin(login);
                user.setPassword(password);
                user.setEmail(email);
                user.setFirstName(firstName);
                user.setLastName(lastName);
                user.setBirthday(birthday);
                user.setRole(new JdbcRoleDao().findByName(role_name));
            }
            conn.commit();
            return user;
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    /**
     * Find in the table users a user with the specified email.
     *
     * @param email the specified email of a user
     * @return user with the specified email
     */
    @Override
    public User findByEmail(String email) {
        if (email == null) {
            throw new NullPointerException();
        }
        if (!isUserByEmail(email)) {
            throw new IllegalArgumentException();
        }
        User user = null;
        Connection conn = createConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn.setTransactionIsolation(
                    Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "LEFT JOIN Roles "
                    + "ON Users.Role_id = Roles.Role_id "
                    + "WHERE email = ?");
            ps.setString(1, email);
            rs = ps.executeQuery();

            while (rs.next()) {
                long id = rs.getLong("USER_ID");
                String login = rs.getString("LOGIN");
                String password = rs.getString("PASSWORD");
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                Date birthday = rs.getDate("BIRTHDAY");
                String role_name = rs.getString("NAME");

                user = new User();
                user.setId(id);
                user.setLogin(login);
                user.setPassword(password);
                user.setEmail(email);
                user.setFirstName(firstName);
                user.setLastName(lastName);
                user.setBirthday(birthday);
                user.setRole(new JdbcRoleDao().findByName(role_name));
            }
            conn.commit();
            return user;
        } catch (SQLException e) {
            logger.error(this, e);
            JdbcUtils.rollbackQuietly(conn);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
    }

    private boolean isUserById(Long user_id) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "WHERE User_id = ?");
            ps.setLong(1, user_id);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }

    private boolean isUserByLogin(String login) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "WHERE login = ?");
            ps.setString(1, login);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }

    private boolean isUserByEmail(String email) {

        Connection conn = createConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps = conn.prepareStatement(
                    "SELECT * FROM Users "
                    + "WHERE Email = ?");
            ps.setString(1, email);
            rs = ps.executeQuery();

            if (rs.next()) {
                return true;
            }
        } catch (SQLException e) {
            logger.error(this, e);
            throw new RuntimeException(e);
        } finally {
            JdbcUtils.closeQuietly(rs);
            JdbcUtils.closeQuietly(ps);
            JdbcUtils.closeQuietly(conn);
        }
        return false;
    }
}
